/*** Eclipse Class Decompiler plugin, copyright (c) 2016 Chen Chao (cnfree2000@hotmail.com) ***/
package com.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	
	
	/**
	   * @Description: 下载excel，仅支持dataList中再嵌套一层list,list格式必须为List<Map<String, Object>>
	   * @param {
	   *	title:导出文件标题
	   *	headers:excel首行名称，例：{ "订单号","订单名称","商品规格", "数量" }
	   *	dataKeys:headers对应的数据键，例： { "orderNo", "orderName","goodsSize", "amount" }
	   *	dataList:数据集合
	   *	response:请求响应
	   * }
	   * @date 2018年11月22日
	   */
	public static void downloadExcel(String title,String[] headers,String[] dataKeys, List<Map<String, Object>> dataList,HttpServletResponse response) throws Exception {
		HSSFWorkbook workbook = creareExcel(title, headers, dataKeys, dataList);
		OutputStream os =null;
		try {
    		response.setCharacterEncoding("UTF-8");
    		response.setContentType("application/octet-stream;charset=UTF-8");
    		response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(title+".xls","utf8"));
    		os = response.getOutputStream();
    		workbook.write(os);
		} finally {
			if (os!=null) {
				os.close();
			}			
		}
	}

	/**
	   * @Description: 生成excel文件，仅支持dataList中再嵌套一层list,list格式必须为List<Map<String, Object>>
	   * @param {
	   *	title:导出文件标题
	   *	headers:excel首行名称，例：{ "订单号","订单名称","商品规格", "数量" }
	   *	dataKeys:headers对应的数据键，例： { "orderNo", "orderName","goodsSize", "amount" }
	   *	dataList:数据集合
	   *	file:excel文件
	   * }
	   * @date 2018年11月22日
	   */
	@SuppressWarnings({ })
	public static File createExcel(String title,String[] headers,String[] dataKeys, List<Map<String, Object>> dataList, File file) throws Exception {		
		HSSFWorkbook workbook = creareExcel(title, headers, dataKeys, dataList);
		FileOutputStream fileOutputStream = null;

		try {
			if (file.exists()) {
				file.delete();
			}
			fileOutputStream = new FileOutputStream(file);
			workbook.write(fileOutputStream);
			// workbook.getBytes();
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} finally {
			if (fileOutputStream != null) {
				fileOutputStream.close();
			}
		}
		return file;
	}

	
	    /**
	    * @Title: creareExcel
	    * @Description: 
	    * @param @param title
	    * @param @param headers
	    * @param @param dataKeys
	    * @param @param dataList
	    * @param @param file
	    * @param @return    参数
	    * @return HSSFWorkbook    返回类型
	    * @throws
	    */
	    
	@SuppressWarnings("unchecked")
	private static HSSFWorkbook creareExcel(String title, String[] headers, String[] dataKeys,
			List<Map<String, Object>> dataList) {

		HSSFWorkbook workbook = new HSSFWorkbook();// 创建excel工作空间
		HSSFSheet sheet = workbook.createSheet(title);// 创建sheet

		// 生成头部样式和字体
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFFont font = workbook.createFont();
		font.setFontName("微软雅黑");// 设置字体
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
		style.setFont(font); // 在样式中引用这种字体

		// 生成内容字体和样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);		
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFFont font2 = workbook.createFont();
		font2.setFontName("微软雅黑");// 设置字体
		font2.setFontHeightInPoints((short) 12);// 设置字体大小
		style2.setFont(font2); // 在样式2中引用这种字体

		// 生成excel头部
		HSSFRow rowHead = sheet.createRow(0);// 创建第一行作为列名
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = rowHead.createCell(i);// 创建单元格
			sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
			cell.setCellStyle(style);
			cell.setCellValue(headers[i]);
		}
		
		
		// 生成excel内容
		int rowNum = 1;// excel第几行,第二行开始
		for (int i = 0; i < dataList.size(); i++) {
			Map<String, Object> dataFirst = dataList.get(i);//第一层数据
			List<Map<String, Object>> dataSecond=null;//第二层数据
			int dataRowNum=1;//该条数据占据行数
			for (Map.Entry<String, Object> entry : dataFirst.entrySet()) {
				//获取该条数据最大占据行数
				if (entry.getValue() instanceof Collection) {
					dataSecond=(List<Map<String, Object>>) entry.getValue();
					if (dataRowNum<dataSecond.size()) {
						dataRowNum=dataSecond.size();
					}
				}
			}
			for (int j = 0; j < dataRowNum; j++) {
				HSSFRow row = sheet.createRow(rowNum);
				// 该条记录第一行
				for (int k = 0; k < headers.length; k++) {
					HSSFCell cell = row.createCell(k);// 第k列
					sheet.autoSizeColumn(k, true);// 根据字段长度自动调整列的宽度
					sheet.setDefaultColumnStyle(k, style2);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 指定单元格格式：数值、公式或字符串
					if (j==0&&dataFirst.get(dataKeys[k])!=null) {
						//第一层数据
						cell.setCellStyle(style2);
						cell.setCellValue(String.valueOf(dataFirst.get(dataKeys[k])==null?"":dataFirst.get(dataKeys[k])));
						// 动态合并单元格-----------------------------------r1,r2,k1,k2
						sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + dataRowNum - 1, k, k));//第r1行到第r2行,第k1列到第k2列
					}else if (dataFirst.get(dataKeys[k])==null) {
						//第二层数据
						cell.setCellStyle(style2);
						cell.setCellValue(String.valueOf(dataSecond.get(j).get(dataKeys[k])==null?"":dataSecond.get(j).get(dataKeys[k])));
						sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, k, k));
					} 
					
				}				
				rowNum++;
			}			
		}
		return workbook;
	}

	/**
	 * @Description: 读取excel内容
	 * @param {
	 *            file：excel文件 }
	 * @date 2018年11月22日
	 */
	public static void readExcel(File file) {
		Workbook book = null;
		try {
			// 读取excel2007
			book = new XSSFWorkbook(new FileInputStream(file));
		} catch (Exception e) {
			try {
				// 读取excel2003
				book = new HSSFWorkbook(new FileInputStream(file));
			} catch (IOException e1) {
				// 文件格式不对
				return;
			}

		}
		Sheet sheet = book.getSheetAt(0);

		for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); ++i) {
			Row row = sheet.getRow(i);
			for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); ++j) {
				String cell = (row.getCell(j) != null) ? row.getCell(j).toString() : "";
				System.out.println(cell);
			}
		}
	}

	
	public static void main(String[] args) throws Exception {
		String[] headers = { "订单号","订单名称","商品规格", "数量" };// excel列名
		String[] dataKeys = { "orderNo", "orderName","goodsSize", "amount" };// 订单对应订单字段
		File file=new File("D:/demo1.xls");
		List<Map<String, Object>> firstList=new ArrayList<>();
		
		Map<String, Object> firstMap=new HashMap<>();
		firstMap.put("orderNo", "1236678907555");
		firstMap.put("orderName", "西侧能起");
		
		List<Map<String, Object>> secondList=new ArrayList<>();
		Map<String, Object> secondMap=new HashMap<>();
		secondMap.put("goodsSize", 11);
		secondMap.put("amount", 123.0);
		secondList.add(secondMap);
		secondList.add(secondMap);
		secondList.add(secondMap);
		
		firstMap.put("list", secondList);
		
		
		firstList.add(firstMap);
		firstList.add(firstMap);
		firstList.add(firstMap);
		
		createExcel("测试", headers, dataKeys, firstList, file);
	}

	/*public static void main(String[] args) throws IOException {
		File file = new File("E:/pic/1.xls");
		readExcel(file);
	}*/
}